# 项目工时信息：
# 月份项目工时：monthProjectWorktime {"$yearMonth" : {"$projectName" : {"$staffName" : "$worktime"}}}

import openpyxl
import os
import re

class WorktimeModel(object):
    projectNames = {}
    projectWorkTime = {}
    myProjectWorkTime = {}
    monthProjectWorktime = {}
    myMonthProjectWorktime = {}
    projectWorkInit = {}



# 数据初始化
def DataInit():
    projectWorkInit = {'招商平安资产基金管理子系统':'10.12',
                       '服务商管理子系统项目':'10.7',
                       '招银国际私募基金管理系统建设项目':'13.2',
                       '青岛国信基金管理系统建设项目':'2.26'}
    return projectWorkInit




# 获取目录里的文件
def dataInput(dir, worktimeModel):

    for folderName, subfolders, filenames in os.walk(dir):
        for filename in filenames:
            name = os.path.basename(filename).split('.')[0]
            if os.path.basename(filename).split('.')[1] == 'xlsx':
                wb = openpyxl.load_workbook(dir + "/" + filename)
                worksheet = wb[name]
                #readWorkTimeBySingleFile(worksheet, name, projectWorkTime, monthProjectWorktime)
                #readProjectNames(worksheet, filename, projectNames)
    #print(projectWorkTime)
    #print(projectNames)
    #print(monthProjectWorktime)
    #myProjects = getProjectNamesByDept(myDept, projectNames)
    #resultData['myProjects'] = getProjectNamesByDept(myDept, projectNames)
    #myProjectWorkTime = getMyProjectWorkTime(myProjects, projectWorkTime)
    #resultData[myProjectWorkTime] = getMyProjectWorkTime(myProjects, projectWorkTime)
    #myMonthProjectWorktime = getMyMonthProjectWorkTime(myProjects, monthProjectWorktime)
    #resultData[myMonthProjectWorktime] = getMyMonthProjectWorkTime(myProjects, monthProjectWorktime)

# 获取所有项目名称 项目信息：projectInfo {projectName : "", projectDepartment : ""}
def getProjectInfo(dir):
    projectInfo = {}
    for folderName, subfolders, filenames in os.walk(dir):
        for filename in filenames:
            name = os.path.basename(filename).split('.')[0]
            if os.path.basename(filename).split('.')[1] == 'xlsx':
                wb = openpyxl.load_workbook(dir + "/" + filename)
                worksheet = wb[name]
                yearMonth = getYearMonth(filename)
                inout = getFileType(filename)
                # 外包
                if inout == '_外勤':
                    projectNameIndex = 11
                    projectDeptIndex = 10
                else:
                    # 内勤
                    projectNameIndex = 5
                    projectDeptIndex = 7
                    for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
                        projectName = row[projectNameIndex].value
                        projectDept = row[projectDeptIndex].value
                        if projectName not in projectInfo.keys():
                            projectInfo[projectName] = {'dept': projectDept}
    return projectInfo

# 获取指定部门的项目列表
def getProjectNamesByDept(dept, projectInfo):
    projectNames = []
    for k in projectInfo.keys():
        if dept == projectInfo[k]['dept']:
            projectNames.append(k)
    # index = projectNames.index('中宏“宏途伟业”项目资源池-产品需求及过程管理组')
    # del projectNames[index]
    return projectNames

## 月份｜项目｜员工｜工时 monthProjectStaffWorktime {"$yearMonth" : {"$projectName" : {"$staffName" : "$workTime"}}}
def getProjectWorktimeDetailByMonth(dir):
    projectWorktimeDetailByMonth = {}
    djProjectInfo = getProjectNamesByDept('多金系统开发部', getProjectInfo(dir))
    print(djProjectInfo)
    for folderName, subfolders, filenames in os.walk(dir):
        for filename in filenames:
            name = os.path.basename(filename).split('.')[0]
            if os.path.basename(filename).split('.')[1] == 'xlsx':
                wb = openpyxl.load_workbook(dir + "/" + filename)
                worksheet = wb[name]
                yearMonth = getYearMonth(filename)
                # 外包
                if getFileType(filename) == '_外勤':
                    projectNameIndex = 11
                    staffNameIndex = 1
                    workTimeIndex = 14
                else:
                    # 内勤
                    projectNameIndex = 5
                    staffNameIndex = 1
                    workTimeIndex = 10
                for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
                    projectName = row[projectNameIndex].value
                    worktime = row[workTimeIndex].value
                    staffName = row[staffNameIndex].value
                    if projectName in djProjectInfo:
                        if yearMonth not in projectWorktimeDetailByMonth.keys():
                            projectWorktimeDetailByMonth[yearMonth] = {projectName : {staffName : worktime}}
                        elif projectName not in projectWorktimeDetailByMonth[yearMonth].keys():
                            projectWorktimeDetailByMonth[yearMonth][projectName] = {staffName : worktime}
                        elif staffName not in projectWorktimeDetailByMonth[yearMonth][projectName].keys():
                            projectWorktimeDetailByMonth[yearMonth][projectName][staffName] = worktime
                        else:
                            projectWorktimeDetailByMonth[yearMonth][projectName][staffName] = round((float(worktime) + float(projectWorktimeDetailByMonth[yearMonth][projectName][staffName])), 2)
    return projectWorktimeDetailByMonth

# 获取项目｜月份｜汇总工时 projectWorkTime {"$projectName" : {"$yearMonth" : {"$staffName" : "$workTime"} } }
def getProjectMonthWorktime(dir):
    projectMonthWorktime = {}
    djProjectInfo = getProjectNamesByDept('多金系统开发部', getProjectInfo(dir))
    print(djProjectInfo)
    for folderName, subfolders, filenames in os.walk(dir):
        for filename in filenames:
            name = os.path.basename(filename).split('.')[0]
            if os.path.basename(filename).split('.')[1] == 'xlsx':
                wb = openpyxl.load_workbook(dir + "/" + filename)
                worksheet = wb[name]
                yearMonth = getYearMonth(filename)
                inout = getFileType(filename)
                # 外包
                if inout == '_外勤':
                    projectNameIndex = 11
                    staffNameIndex = 1
                    workTimeIndex = 14
                else:
                    # 内勤
                    projectNameIndex = 5
                    staffNameIndex = 1
                    workTimeIndex = 10
                for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
                    projectName = row[projectNameIndex].value
                    worktime = row[workTimeIndex].value
                    if projectName in djProjectInfo:
                        if projectName not in projectMonthWorktime.keys():
                            projectMonthWorktime[projectName] = {yearMonth: worktime}
                        else:
                            if yearMonth not in projectMonthWorktime[projectName].keys():
                                projectMonthWorktime[projectName][yearMonth] = worktime
                            else:
                                worktime1 = float(worktime)
                                worktime2 = float(projectMonthWorktime[projectName][yearMonth])
                                worktimesum = round((worktime1 + worktime2), 2)
                                projectMonthWorktime[projectName][yearMonth] = worktimesum
    return projectMonthWorktime


# 输出结果
def dataOutput(projectWorkInit, myProjectWorkTime, monthProjectStaffWorktime):
    sheetData = []
    headLine = ['项目名称', '收入(万元)', '收入(工时)', '去年工时', '2022年01月', '2022年02月', '2022年03月', '2022年04月','2022年05月', '2022年06月','2022年07月', '2022年08月','2022年09月', '2022年10月','2022年11月', '2022年12月']
    sheetData.append(headLine)
    for k in myProjectWorkTime.keys():
        dataLine = []
        dataLine.append(k)
        dataLine.append(0)
        dataLine.append(0)
        if k in projectWorkInit.keys():
            dataLine.append(projectWorkInit.get(k))
        else:
            dataLine.append(0)
        for i in range(len(headLine)):
            if i>3:
                index = headLine[i]
                projecttime = myProjectWorkTime[k]
                if index in projecttime.keys():
                    dataLine.append(float(projecttime[index]))
                else:
                    dataLine.append(0)
        sheetData.append(dataLine)
    #print(sheetData)

    wb = openpyxl.Workbook()
    # 汇总sheet页
    ws = wb.worksheets[0]
    rowIdx = 1
    for i in range(len(sheetData)):
        line = sheetData[i]
        if i == 0:
            ws["A" + str(rowIdx)].value = line[0]
            ws["B" + str(rowIdx)].value = line[1]
            ws["C" + str(rowIdx)].value = line[2]
            ws["D" + str(rowIdx)].value = line[3]
            ws["E" + str(rowIdx)].value = line[4]
            ws["F" + str(rowIdx)].value = line[5]
            ws["G" + str(rowIdx)].value = line[6]
            ws["H" + str(rowIdx)].value = line[7]
            ws["I" + str(rowIdx)].value = line[8]
            ws["J" + str(rowIdx)].value = line[9]
            ws["K" + str(rowIdx)].value = line[10]
            ws["L" + str(rowIdx)].value = line[11]
            ws["M" + str(rowIdx)].value = line[12]
            ws["N" + str(rowIdx)].value = line[13]
            ws["O" + str(rowIdx)].value = line[14]
            ws["P" + str(rowIdx)].value = line[15]
        else:
            ws["A" + str(rowIdx)].value = line[0]
            ws["B" + str(rowIdx)].value = line[1]
            ws["C" + str(rowIdx)].value = line[2]
            ws["D" + str(rowIdx)].value = line[3]
            ws["E" + str(rowIdx)].value = round(float(line[4])/21, 2)
            ws["F" + str(rowIdx)].value = round(float(line[5])/16, 2)
            ws["G" + str(rowIdx)].value = round(float(line[6])/23, 2)
            ws["H" + str(rowIdx)].value = round(float(line[7])/21, 2)
            ws["I" + str(rowIdx)].value = round(float(line[8])/20, 2)
            ws["J" + str(rowIdx)].value = round(float(line[9])/21, 2)
            ws["K" + str(rowIdx)].value = round(float(line[10])/21, 2)
            ws["L" + str(rowIdx)].value = round(float(line[11])/23, 2)
            ws["M" + str(rowIdx)].value = round(float(line[12])/21, 2)
            ws["N" + str(rowIdx)].value = round(float(line[13])/21, 2)
            ws["O" + str(rowIdx)].value = round(float(line[14])/21, 2)
            ws["P" + str(rowIdx)].value = round(float(line[15])/21, 2)
        rowIdx += 1

    # 月份sheet页
    monthList = ['2022年01月', '2022年02月', '2022年03月', '2022年04月', '2022年05月', '2022年06月',
                 '2022年07月', '2022年08月', '2022年09月', '2022年10月', '2022年11月', '2022年12月']
    for month in monthList:
        print(month)
        ws = wb.create_sheet(month[5:])
        projectStaffTime = monthProjectStaffWorktime[month]
        row = list(projectStaffTime.keys())
        col = []
        for project in projectStaffTime.keys():
            for staff in projectStaffTime[project].keys():
                if staff not in col:
                    col.append(staff)
        print(projectStaffTime)


    wb.save("工时统计.xlsx")
    #print(myProjectWorkTime)




# 获取指定项目的工时
# def getMyProjectWorkTime(myProjectNames, projectWorkTime):
#     for k in projectWorkTime.keys():
#         if k in myProjectNames:
#             myProjectWorkTime[k] = projectWorkTime.get(k)
#     #print(myProjectWorkTime)
#     return myProjectWorkTime

# 获取指定项目的月份个人工时
def getMyMonthProjectWorkTime(myProjectNames, monthProjectWorktime):
    for yearmonth in monthProjectWorktime.keys():
        projectWorktime = {}
        for projectName in monthProjectWorktime[yearmonth].keys():
            if projectName in myProjectNames:
                projectWorktime[projectName] = monthProjectWorktime[yearmonth][projectName]
        monthProjectWorktime[yearmonth] = projectWorktime
    return monthProjectWorktime

# 获取文件名的年月
def getYearMonth(filename):
    yearMonthRegex = re.compile(r'\d{4}年\d{2}月')
    mo = yearMonthRegex.search(filename)
    return mo.group()

# 获取文件名的内外包
def getFileType(filename):
    fileType = re.compile(r'_\w{2}')
    mo = fileType.search(filename)
    return mo.group()

# 读取工时
def readWorkTimeBySingleFile(worksheet, filename, projectWorkTime, monthProjectWorktime):
    yearMonth = getYearMonth(filename)
    inout = getFileType(filename)
    # 外包
    if inout == '_外勤':
        projectNameIndex = 11
        staffNameIndex = 1
        workTimeIndex = 14
    else:
        # 内勤
        projectNameIndex = 5
        staffNameIndex = 1
        workTimeIndex = 10
    for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
        projectName = row[projectNameIndex].value
        staffName = row[staffNameIndex].value
        worktime = row[workTimeIndex].value

        ## 组装projectWorkTime
        if projectName not in projectWorkTime.keys():
            projectWorkTime[projectName] = {yearMonth: worktime}
        else:
            if yearMonth not in projectWorkTime[projectName].keys():
                projectWorkTime[projectName][yearMonth] = worktime
            else:
                worktime1 = float(worktime)
                worktime2 = float(projectWorkTime[projectName][yearMonth])
                worktimesum = round((worktime1 + worktime2), 2)
                projectWorkTime[projectName][yearMonth] = worktimesum

        ## 组装monthProjectWorktime
        if yearMonth not in monthProjectWorktime.keys():
            monthProjectWorktime[yearMonth] = {projectName : {staffName : worktime}}
        else:
            if projectName not in monthProjectWorktime[yearMonth].keys():
                monthProjectWorktime[yearMonth][projectName] = {staffName : worktime}
            else:
                if staffName not in monthProjectWorktime[yearMonth][projectName].keys():
                    monthProjectWorktime[yearMonth][projectName][staffName] = worktime
                else:
                    monthProjectWorktime[yearMonth][projectName][staffName] += worktime

if __name__ == '__main__':
    projectWorkInit = DataInit()
    #dir = '/Users/zhangxiangbin/awesome-doc/02 招商金科/工时统计/2022年/'
    dir = '/Users/zhangxiangbin/Desktop/工时统计/'
    #projectMonthWorktime = getProjectMonthWorktime(dir)
    #print(projectMonthWorktime)
    projectWorktimeDetailByMonth = getProjectWorktimeDetailByMonth(dir)
    print(projectWorktimeDetailByMonth)
    #dataOutput(projectWorkInit, projectMonthWorktime, monthProjectStaffWorktime)
